﻿using System;
using System.Configuration;
using System.Data; 
using System.Collections;
using System.IO;
using System.Text;
using System.Reflection;
using System.Windows.Forms;
using System.Collections.Generic;
using SNPAD;
using System.Data.OleDb;

namespace SNPAD
{
    internal class OleDbHelper3 
    {
        public static readonly string ConnectionString = SNPAD.Configuration.Instance.GetString(ConfigurationKeys.ConnectionString);
        //  public static readonly string ConnectionString = "Data Source='" + FileName + "';password = 'sa';";

        public static OleDbConnection    m_conn = null;

        private static object m_lockObject = new object();

        private static OleDbCommand cmd = null;

        public static object LockObject
        {
            get { return m_lockObject; }
        }

        public static OleDbConnection Conn
        {
            get
            {
                OpenConnection();
                return m_conn;
            }
        }

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        #region ExecuteNonQuery
        /// <summary>
        /// Execute a OleDbCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a OleDbConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-OleDb command</param>
        /// <param name="commandParameters">an array of OleDbParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string cmdText, params OleDbParameter[] commandParameters)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }

                PrepareCommand(cmd, null, null, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a OleDbCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-OleDb command</param>
        /// <param name="commandParameters">an array of OleDbParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, connection, null, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        public static int ExecuteNonQuery(OleDbConnection connection, string cmdText)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, connection, null, cmdText, null);
                int val = cmd.ExecuteNonQuery();
                return val;
            }
        }
        /// <summary>
        /// Execute a OleDbCommand (that returns no resultset) using an existing OleDb Transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing OleDb transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-OleDb command</param>
        /// <param name="commandParameters">an array of OleDbParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, (OleDbConnection)trans.Connection, trans, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }



        #endregion

        public static OleDbDataReader ExecuteReader(string cmdText, params OleDbParameter[] commandParameters)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, null, null, cmdText, commandParameters);
                OleDbDataReader rdr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return rdr;
            }
        }

        public static object ExecuteScalar(string cmdText, params OleDbParameter[] commandParameters)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, null, null, cmdText, commandParameters);
                object o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return o;
            }
        }

        public static object ExecuteScalar(string cmdText)
        {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, null, null, cmdText);
                object o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return o;
            }
        }
        public static DataTable ExecuteDataTable(string cmdText, params OleDbParameter[] commandParameters)
         {
            lock (LockObject)
            {
                if (cmd == null)
                {
                    cmd = new OleDbCommand();
                }
                PrepareCommand(cmd, null, null, cmdText, commandParameters);
                OleDbDataAdapter adp = new OleDbDataAdapter();
                adp.SelectCommand = cmd;
                DataTable tb = new DataTable();
                adp.Fill(tb);
                cmd.Parameters.Clear();
                return tb;
            }
        }

        #region PrepareCommand
        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">OleDbCommand object</param>
        /// <param name="conn">OleDbConnection object</param>
        /// <param name="trans">OleDbTransaction object</param>
        /// <param name="cmdText">Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">OleDbParameters to use in the command</param>
        //private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        //{
        //    if (conn != null)
        //    {
        //        if (conn.State != ConnectionState.Open)
        //            conn.Open();
        //        cmd.Connection = conn;
        //    }
        //    else 
        //    {
        //        OpenConnection();
        //        cmd.Connection = m_conn;
        //    }

        //    if (cmdParms != null)
        //    {
        //        cmd.Parameters.AddRange(cmdParms);
        //    }

        //    if (cmd.CommandText.Equals(cmdText))
        //    {
        //        return;
        //    }

        //    cmd.CommandText = cmdText;

        //    //logger.Debug("PrepareCommand() CommandText " + cmdText);

        //    if (trans != null) 
        //    {
        //        //logger.Debug("PrepareCommand() Transaction");
        //        cmd.Transaction = trans;
        //    }
        //}
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn != null)
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;
            }
            else
             {
                OpenConnection();
                cmd.Connection = m_conn;
            }

            if (cmdParms != null)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(cmdParms);
            }
            else
            {
                cmd.Parameters.Clear();
            }

            cmd.CommandText = cmdText;

            //logger.Debug("PrepareCommand() CommandText " + cmdText);

            if (trans != null)
            {
                //logger.Debug("PrepareCommand() Transaction");
                cmd.Transaction = trans;
            }
        }


        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText)
        {
            if (conn != null)
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                cmd.Connection = conn;
            }
            else
            {
                OpenConnection();
                cmd.Connection = m_conn;
            }

            cmd.Parameters.Clear();

            cmd.CommandText = cmdText;

            //logger.Debug("PrepareCommand() CommandText " + cmdText);

            if (trans != null)
            {
                //logger.Debug("PrepareCommand() Transaction");
                cmd.Transaction = trans;
            }
        }

        #endregion

        public static void OpenConnection( )
        {
            if (m_conn == null)
            {
                m_conn = new OleDbConnection(ConnectionString);
            }

            if (m_conn.State != ConnectionState.Open)
            { 
                m_conn.Open();
            }
        }

        public static void CloseConnection()
        {
            if (m_conn != null)
            {
                
                m_conn.Close();
            }
        }

        public static OleDbTransaction GetTransaction()
        {
            OpenConnection(); 
            return m_conn.BeginTransaction();
        }

        #region CacheParameters
        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of OleDbParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }
        #endregion

        #region GetCachedParameters
        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached OleDbParamters array</returns>
        public static OleDbParameter[] GetCachedParameters(string cacheKey)
        {
            OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }
        #endregion


        #region  New Method of DataManager
        public static DataSet OleDbToRecordSet(string vOleDbStr)
        {
            DataSet functionReturnValue = default(DataSet);
            DataSet rs = default(DataSet);
            rs = new DataSet();
            functionReturnValue = null;
            try
            {
                OleDbCommand cmd = default(OleDbCommand);
                OleDbDataAdapter ad = default(OleDbDataAdapter);
                cmd = new OleDbCommand(vOleDbStr, m_conn);
                cmd.CommandType = CommandType.Text;
                ad = new OleDbDataAdapter(cmd);
                ad.Fill(rs);
                ad.Dispose();
                cmd.Dispose();
                functionReturnValue = rs;
            }
            catch (OleDbException ex)
            {
                ErrorDisplay_OleDb(ex);
            }
            return functionReturnValue;
        }
        public static bool ErrorDisplay_OleDb(OleDbException expOleDb)
        {

            foreach (OleDbError errOleDb in expOleDb.Errors)
            {
                MessageBox.Show( errOleDb.Source  + " " + errOleDb.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);

            }
            return false;
        }
        public static bool CheckRec(string sOleDb)
        {
            bool functionReturnValue = false;
            functionReturnValue = false;
            if (sOleDb.Length == 0)
            {
                return functionReturnValue;
            }
            try
            {
                OleDbDataReader dr = default(OleDbDataReader);
                OleDbCommand command = new OleDbCommand(sOleDb, m_conn);
                dr = command.ExecuteReader();
                if (dr.Read() == true)
                {
                    functionReturnValue = true;
                }
                else
                {
                    functionReturnValue = false;
                }
                dr.Close();
                dr.Dispose();
                dr = null;
                command.Dispose();
                command = null;
            }
            catch (OleDbException ex)
            {
                ErrorDisplay_OleDb(ex);
            }
            return functionReturnValue;
        }
        #endregion

        #region

        public static int UpdateByDataSet(DataSet ds, string OleDbStr, string strTblName)
        {
            try
            {

                OleDbDataAdapter myAdapter = new OleDbDataAdapter();
                //OleDbCommand myCommand = new OleDbCommand(OleDbStr, m_conn);
                //myAdapter.SelectCommand = myCommand;
                myAdapter.SelectCommand = new OleDbCommand(OleDbStr, m_conn);
                OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(myAdapter);


                //myAdapter.TableMappings.Add("Table", strTblName);//"Table1"第二个表，"Table2"第二个表，


                myAdapter.Fill(ds, strTblName);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    //ds.Tables[0].Rows[i].SetAdded();
                    ds.Tables[0].Rows[i].SetModified();
                }

                myAdapter.Update(ds, strTblName);

                return 0;

            }
            catch (OleDbException ex)
            {
                 
                ErrorDisplay_OleDb(ex);
                return -1;
            }
        }
        #endregion
        #region DataGridView数据映射
        public static OleDbDataAdapter OleDb_Bind(string Select_String,  DataTable SDT, DataGridView DG_Name)
        {
            OleDbDataAdapter SDA = new OleDbDataAdapter();

            OleDbCommand SCMD = new OleDbCommand(Select_String, m_conn);
            try
            {

                SDA.SelectCommand = SCMD;
                SDT.Clear();
                SDA.Fill(SDT);
                DG_Name.DataSource = SDT;
                 
            }
            catch { }
            finally
            {
                if (SDA != null) ((IDisposable)SDA).Dispose();
                if (SCMD != null) ((IDisposable)SCMD).Dispose();
            }
             
            return SDA;
        }
        #endregion


        #region 调用备份等待窗体
        public static void Wait_Form()
        {
            //try
            //{
            //    WaitForm WF = new WaitForm();
            //    WF.ShowDialog();
            //}
            //catch { }
        }
        #endregion
        //public static DataTable SelectSalePage(int pageIndex, int pageSize, string tableName, string orderField, string orderType, string strWhere,string group, string returnField, out int tolPage, out int tolRecord)
        //{
     

        // //   OleDbCommand OleDbcmd = new OleDbCommand();
        // //   OleDbcmd.Connection = m_conn;
        // //   OleDbcmd.CommandType = CommandType.StoredProcedure;
        // //   OleDbcmd.CommandText = "pzging";

        // //   List<OleDbParameter> pars = new List<OleDbParameter> {
        // //       new OleDbParameter("@pageIndex",pageIndex),
        // //       new OleDbParameter("@pageSize",pageSize),
        // //       new OleDbParameter("@tableName",tableName),
        // //       new OleDbParameter("@orderField",orderField),
        // //       new OleDbParameter("@orderType",orderType),
        // //       new OleDbParameter("@strWhere",strWhere),
        // //       new OleDbParameter("@group",group),
        // //       new OleDbParameter("@returnField",returnField)
        // //   };
        // //   OleDbcmd.Parameters.AddRange(pars.ToArray());

        // //   OleDbParameter out_tolPage = new OleDbParameter("@tolPage", OleDbDbType.Int);
        // //   out_tolPage.Direction = ParameterDirection.Output;
        // //   OleDbcmd.Parameters.Add(out_tolPage);

        // //   OleDbParameter out_tolRecord = new OleDbParameter("@tolRecord", OleDbDbType.Int);
        // //   out_tolRecord.Direction = ParameterDirection.Output;
        // //   OleDbcmd.Parameters.Add(out_tolRecord);

        // //   OleDbDataAdapter OleDbadap = new OleDbDataAdapter();
        // //OleDbadap.SelectCommand = OleDbcmd;

        // //   DataSet ds = new DataSet();
        // //   OleDbadap.Fill(ds);

        // //   tolPage = Convert.ToInt32(out_tolPage.Value);
        // //   tolRecord = Convert.ToInt32(out_tolRecord.Value);

        // //   return ds.Tables[0];


        //}
         
        }


    
}

